﻿CREATE PROCEDURE [acms].[PostPosition_BuildTree]
	@ParentGuid uniqueidentifier, 
	@PublishDate DateTime
AS
	if @ParentGuid is null begin
		if(@PublishDate is null) begin
			insert into PostPositions
				(PostId, PublishDate)
			select Id, @PublishDate
			from acms.Post_View with(nolock)
			where ParentGuid is null and PublishFrom is null and PublishTo is null
		end
		else begin 
			insert into PostPositions
				(PostId, PublishDate)
			select Id, @PublishDate
			from acms.Post_View
			where ParentGuid is null and PublishFrom <= @PublishDate and isNull(PublishTo, '9999/12/31') > @PublishDate
		end	
	end
	else begin
		if @PublishDate is null begin
			insert into PostPositions
				(PostId, PublishDate)
			select Id, @PublishDate
			from acms.Post_View
			where ParentGuid = @ParentGuid and PublishFrom is null and PublishTo is null
		end
		else begin 
			insert into PostPositions
				(PostId, PublishDate)
			select Id, @PublishDate
			from acms.Post_View
			where ParentGuid = @ParentGuid and PublishFrom <= @PublishDate and isNull(PublishTo, '9999/12/31') > @PublishDate
		end	
	end	
	
	--do for children
	declare @PostGuid uniqueidentifier
	
	declare cur cursor local for
	
	select a.PostGuid
	From acms.Post_View a
		inner join acms.PostPositions b on a.Id = b.PostId and isNull(b.PublishDate, '9999/12/31') = isnull(@PublishDate, '9999/12/31')
	where
		(@ParentGuid is not null and a.ParentGuid = @ParentGuid )
		or 
		(@ParentGuid is null and a.ParentGuid is null)

	open cur
	
	fetch next from cur into @PostGuid
	while @@fetch_status = 0 begin
		exec [acms].[PostPosition_BuildTree] @PostGuid, @PublishDate
		
		fetch next from cur into @PostGuid
	end
	
	close cur
	
	deallocate cur
RETURN 0;